/*
	EXEC SP_LOAD_CREDIT_NOTES 0,'','', '0', '0', '', ''
*/
ALTER PROC SP_LOAD_CREDIT_NOTES @CUSTOMER_ID INT,
				@SO_NO VARCHAR(50),
				@CN_NO VARCHAR(50),
				@NOTE_TYPE VARCHAR(20),
				@STATUS VARCHAR(50),
				@FROM_DATE VARCHAR(10),
				@TO_DATE VARCHAR(10)
AS
BEGIN
	DECLARE @CREDITNOTE_ID INT
	DECLARE @CREDITNOTE_NO VARCHAR(50)
	DECLARE @SALESORDER_NO VARCHAR(50)
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @CN_STATUS VARCHAR(50)
	DECLARE @CN_FINAL_STATUS VARCHAR(100)
	DECLARE @IS_CREDIT_NOTE INT
	DECLARE @ISSUE_DATE VARCHAR(10)
	DECLARE @TOTAL_AMOUNT FLOAT

	DECLARE @RESULTS_TABLE TABLE ( CREDITNOTE_ID INT, CREDITNOTE_NO VARCHAR(50), SALESORDER_NO VARCHAR(50), CUSTOMER_NAME VARCHAR(65), STATUS VARCHAR(50), FINAL_STATUS VARCHAR(100), IS_CREDIT_NOTE INT, ISSUE_DATE VARCHAR(10), TOTAL_AMOUNT FLOAT )

	DECLARE @SQL_STRING VARCHAR(500)
	
	SET @SQL_STRING = 'DECLARE CREDIT_NOTES_CURSOR CURSOR FOR SELECT CNT.CREDITNOTE_ID, CNT.CREDITNOTE_NO, SO.SALESORDER_NO, C.C_NAME, CNT.STATUS, CNT.IS_CREDIT_NOTE, CONVERT(VARCHAR, ISSUE_DATE, 101) AS ISSUE_DATE 
				FROM CREDIT_NOTE_TABLE CNT, SALES_ORDER SO, CUSTOMER C 
			WHERE SO.SALESORDER_ID = CNT.SALESORDER_ID AND C.CUSTOMER_ID = SO.CUSTOMER_ID '
	
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 0 )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND SO.CUSTOMER_ID = ' + CONVERT ( VARCHAR, @CUSTOMER_ID )
	 END

	IF ( @SO_NO IS NOT NULL AND LEN(LTRIM(RTRIM(@SO_NO))) > 0 )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND SO.SALESORDER_NO LIKE ''' + LTRIM(RTRIM(@SO_NO)) + '%'''
	 END

	IF ( @CN_NO IS NOT NULL AND LEN(LTRIM(RTRIM(@CN_NO))) > 0 )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND CNT.CREDITNOTE_NO LIKE ''' + LTRIM(RTRIM(@CN_NO)) + '%'''
	 END
	
	IF ( @NOTE_TYPE IS NOT NULL AND @NOTE_TYPE != '0' )
	 BEGIN
		IF ( @NOTE_TYPE = 'Credit' )
			SET @SQL_STRING = @SQL_STRING + ' AND CNT.IS_CREDIT_NOTE = 0 '
		ELSE
			SET @SQL_STRING = @SQL_STRING + ' AND CNT.IS_CREDIT_NOTE = 1 '
	 END
	
	IF ( @STATUS IS NOT NULL AND @STATUS != '0' )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND CNT.STATUS LIKE ''' + LTRIM(RTRIM(@STATUS)) + ''''
	 END

	IF ( @FROM_DATE IS NOT NULL AND @FROM_DATE != '' )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND CNT.ISSUE_DATE >= ''' + @FROM_DATE + ''''
	 END

	IF ( @TO_DATE IS NOT NULL AND @TO_DATE != '' )
	 BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND CNT.ISSUE_DATE <= ''' + @TO_DATE + ''''
	 END

	EXEC (@SQL_STRING)
	OPEN CREDIT_NOTES_CURSOR
	FETCH NEXT FROM CREDIT_NOTES_CURSOR INTO @CREDITNOTE_ID, @CREDITNOTE_NO, @SALESORDER_NO, @CUSTOMER_NAME, @CN_STATUS, @IS_CREDIT_NOTE, @ISSUE_DATE
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		SET @CN_FINAL_STATUS = ''
		IF ( @CN_STATUS = 'CANCELLED' )
		 BEGIN
			SET @CN_FINAL_STATUS = 'CANCELLED'
		 END
		ELSE
		 BEGIN
			--CHECKING FOR FINANCE HOLD
			IF ( (SELECT  COUNT(*) FROM CREDIT_NOTE_TABLE CNT, CREDIT_NOTE_LINE_ITEM_TABLE CNLT, CREDIT_NOTE_HOLDS_TABLE CNHT WHERE CNLT.CN_ID = CNT.CREDITNOTE_ID AND CNHT.CNLT_ID = CNLT.CNLT_ID AND HOLD_REASON_CODE != '' AND CNT.CREDITNOTE_ID = @CREDITNOTE_ID AND CNHT.HOLD_BY_GROUP = 'FINANCE') > 0 )
			 BEGIN
				SET @CN_FINAL_STATUS = @CN_FINAL_STATUS + ' FINANCE'
			 END
			/*
			--CHECKING FOR MARKETING HOLD		
			IF ( (SELECT  COUNT(*) FROM CREDIT_NOTE_TABLE CNT, CREDIT_NOTE_LINE_ITEM_TABLE CNLT, CREDIT_NOTE_HOLDS_TABLE CNHT WHERE CNLT.CN_ID = CNT.CREDITNOTE_ID AND CNHT.CNLT_ID = CNLT.CNLT_ID AND HOLD_REASON_CODE != '' AND CNT.CREDITNOTE_ID = @CREDITNOTE_ID AND CNHT.HOLD_BY_GROUP = 'MARKETING') > 0 )
			 BEGIN
				SET @CN_FINAL_STATUS = @CN_FINAL_STATUS + ' MARKETING'
			 END
			
			--CHECKING FOR OPERATIONS HOLD		
			IF ( (SELECT  COUNT(*) FROM CREDIT_NOTE_TABLE CNT, CREDIT_NOTE_LINE_ITEM_TABLE CNLT, CREDIT_NOTE_HOLDS_TABLE CNHT WHERE CNLT.CN_ID = CNT.CREDITNOTE_ID AND CNHT.CNLT_ID = CNLT.CNLT_ID AND HOLD_REASON_CODE != '' AND CNT.CREDITNOTE_ID = @CREDITNOTE_ID AND CNHT.HOLD_BY_GROUP = 'OPERATIONS') > 0 )
			 BEGIN
				SET @CN_FINAL_STATUS = @CN_FINAL_STATUS + ' OPERATIONS'
			 END
	
			--CHECKING FOR SALES HOLD		
			IF ( (SELECT  COUNT(*) FROM CREDIT_NOTE_TABLE CNT, CREDIT_NOTE_LINE_ITEM_TABLE CNLT, CREDIT_NOTE_HOLDS_TABLE CNHT WHERE CNLT.CN_ID = CNT.CREDITNOTE_ID AND CNHT.CNLT_ID = CNLT.CNLT_ID AND HOLD_REASON_CODE != '' AND CNT.CREDITNOTE_ID = @CREDITNOTE_ID AND CNHT.HOLD_BY_GROUP = 'SALES') > 0 )
			 BEGIN
				SET @CN_FINAL_STATUS = @CN_FINAL_STATUS + ' SALES'
			 END
			*/
	
			IF ( @CN_FINAL_STATUS != '' )
			 BEGIN
				SET @CN_FINAL_STATUS = 'HOLD BY -' + @CN_FINAL_STATUS
			 END
			ELSE
			 BEGIN
				SET @CN_FINAL_STATUS = 'APPROVED'
			 END
		 END
		IF ( @CN_FINAL_STATUS = 'CANCELLED' )
		 BEGIN
			SELECT @TOTAL_AMOUNT = SUM(DISCOUNT_AMOUNT) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CREDITNOTE_ID
		 END
		ELSE
		 BEGIN
			SELECT @TOTAL_AMOUNT = SUM(DISCOUNT_AMOUNT) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CREDITNOTE_ID AND STATUS != 'CANCELLED'
		 END
		INSERT INTO @RESULTS_TABLE VALUES ( @CREDITNOTE_ID, @CREDITNOTE_NO, @SALESORDER_NO, @CUSTOMER_NAME, @CN_STATUS, @CN_FINAL_STATUS, @IS_CREDIT_NOTE, @ISSUE_DATE, @TOTAL_AMOUNT )
		
		SET @CREDITNOTE_ID = NULL
		SET @CREDITNOTE_NO = NULL
		SET @SALESORDER_NO = NULL
		SET @CUSTOMER_NAME = NULL
		SET @CN_STATUS = NULL
		SET @IS_CREDIT_NOTE = 0
		SET @ISSUE_DATE = NULL
		SET @TOTAL_AMOUNT = 0

		FETCH NEXT FROM CREDIT_NOTES_CURSOR INTO @CREDITNOTE_ID, @CREDITNOTE_NO, @SALESORDER_NO, @CUSTOMER_NAME, @CN_STATUS, @IS_CREDIT_NOTE, @ISSUE_DATE
	 END
	CLOSE CREDIT_NOTES_CURSOR
	DEALLOCATE CREDIT_NOTES_CURSOR

	SELECT * FROM @RESULTS_TABLE ORDER BY CAST(ISSUE_DATE AS DATETIME) DESC,CREDITNOTE_NO DESC
END